﻿using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using DTO;
using System.Data;
namespace DAL
{
    public class CustomerDAL
    {

        #region[Customer_SelectALL]
        public List<DTO.CustomerDTO> Customer_SelectALL()
        {
            List<DTO.CustomerDTO> cusall = new List<DTO.CustomerDTO>();
            Connection connecttion = new Connection();
            MySqlConnection con = new MySqlConnection(connecttion.Connect);
            MySqlCommand cmd = new MySqlCommand("SelectAllCustomer", con);
            cmd.CommandType = System.Data.CommandType.StoredProcedure;

            con.Open();
            MySqlDataReader dr = cmd.ExecuteReader();
            while (dr.Read())
            {
                DTO.CustomerDTO cus = new DTO.CustomerDTO();
                cus.CustomerID   = dr.GetInt32(0);
                cus.Name        = dr.GetString(1);
                cus.Address     = dr.GetString(2);
                cus.Phone       = dr.GetString(3);
                cusall.Add(cus);
            }
            dr.Close();
            con.Close();

            return cusall;
        }
        #endregion

        #region[Customer_SelectByName]
        public List<DTO.CustomerDTO> Custummer_SelectByName(String name)
        {
            List<CustomerDTO> listcus = new List<CustomerDTO>();
            
            Connection connecttion = new Connection();
            MySqlConnection con = new MySqlConnection(connecttion.Connect);
            MySqlCommand cmd = new MySqlCommand("SelectCustomerByName", con);
            cmd.CommandType = System.Data.CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@_name", name);
            con.Open();
            MySqlDataReader dr = cmd.ExecuteReader();
            while (dr.Read())
            {
                DTO.CustomerDTO cus = new DTO.CustomerDTO();
                cus.CustomerID   = dr.GetInt32(0);
                cus.Name        = dr.GetString(1);
                cus.Address     = dr.GetString(2);
                cus.Phone       = dr.GetString(3);
                listcus.Add(cus);
            }
            dr.Close();
            con.Close();
            return listcus;
        }
        #endregion

        #region[Customer_SelectByID]
        public DTO.CustomerDTO Custummer_SelectByID(int id)
        {
            DTO.CustomerDTO cus = new DTO.CustomerDTO();
            Connection connecttion = new Connection();
            MySqlConnection con = new MySqlConnection(connecttion.Connect);
            MySqlCommand cmd = new MySqlCommand("SelectCustomerByID", con);
            cmd.CommandType = System.Data.CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@customerid", id);
            con.Open();
            MySqlDataReader dr = cmd.ExecuteReader();
            while (dr.Read())
            {
                cus.CustomerID   = dr.GetInt32(0);
                cus.Name        = dr.GetString(1);
                cus.Address     = dr.GetString(2);
                cus.Phone       = dr.GetString(3);
            }
            dr.Close();
            con.Close();
            return cus;
        }
        #endregion

        #region[Customer_SelectCustomerWithBillByBillID]
        public DataTable Customer_SelectCustomerWithBillByBillID(int id)
        {
            DataTable dataTable = new DataTable();
            Connection connecttion = new Connection();
            MySqlConnection con = new MySqlConnection(connecttion.Connect);
            MySqlCommand cmd = new MySqlCommand("SelectCustomerWithBillByBillID", con);
            cmd.CommandType = System.Data.CommandType.StoredProcedure;
            cmd.Parameters.Add(new MySqlParameter("@id", id));

            con.Open();
            using (MySqlDataAdapter da = new MySqlDataAdapter(cmd))
            {
                da.Fill(dataTable);
            }
            con.Close();

            return dataTable;
        }
        #endregion

        #region[Customer_SelectCustomerWithBillByID]
        public DataTable Customer_SelectCustomerWithBillByID(int id)
        {
            DataTable dataTable = new DataTable();
            Connection connecttion = new Connection();
            MySqlConnection con = new MySqlConnection(connecttion.Connect);
            MySqlCommand cmd = new MySqlCommand("SelectCustomerWithBillByID", con);
            cmd.CommandType = System.Data.CommandType.StoredProcedure;
            cmd.Parameters.Add(new MySqlParameter("@id", id));

            con.Open();
            using (MySqlDataAdapter da = new MySqlDataAdapter(cmd))
            {
                da.Fill(dataTable);
            }
            con.Close();

            return dataTable;
        }
        #endregion

        #region[Customer_SelectCustomerWithBillByName]
        public DataTable Customer_SelectCustomerWithBillByName(string name)
        {
            DataTable dataTable = new DataTable();
            Connection connecttion = new Connection();
            MySqlConnection con = new MySqlConnection(connecttion.Connect);
            MySqlCommand cmd = new MySqlCommand("SelectCustomerWithBillByName", con);
            cmd.CommandType = System.Data.CommandType.StoredProcedure;
            cmd.Parameters.Add(new MySqlParameter("@name", name));

            con.Open();
            using (MySqlDataAdapter da = new MySqlDataAdapter(cmd))
            {
                da.Fill(dataTable);
            }
            con.Close();

            return dataTable;
        }
        #endregion

        #region [Customer_Insert]
        public bool Customer_Insert(DTO.CustomerDTO cus)
        {
            Connection connecttion = new Connection();
            MySqlConnection con = new MySqlConnection(connecttion.Connect);
            MySqlCommand cmd = new MySqlCommand("AddCustomer", con);
            cmd.CommandType = System.Data.CommandType.StoredProcedure;

            cmd.Parameters.Add(new MySqlParameter("@name",cus.Name));
            cmd.Parameters.Add(new MySqlParameter("@address", cus.Address));
            cmd.Parameters.Add(new MySqlParameter("@phone", cus.Phone));

            con.Open();
            cmd.ExecuteNonQuery();
            con.Close();

            return true;
        }
        #endregion

        #region [Customer_Update]
        public bool Customer_Update(DTO.CustomerDTO cus)
        {
            Connection connecttion = new Connection();
            MySqlConnection con = new MySqlConnection(connecttion.Connect);
            MySqlCommand cmd = new MySqlCommand("UpdateCustomer", con);
            cmd.CommandType = System.Data.CommandType.StoredProcedure;

            cmd.Parameters.Add(new MySqlParameter("@customerid", cus.CustomerID));
            cmd.Parameters.Add(new MySqlParameter("@name", cus.Name));
            cmd.Parameters.Add(new MySqlParameter("@address", cus.Address));
            cmd.Parameters.Add(new MySqlParameter("@phone", cus.Phone));

            con.Open();
            cmd.ExecuteNonQuery();
            con.Close();

            return true;
        }
        #endregion

        #region [Customer_Delete]
        public bool Customer_DeleteByID(DTO.CustomerDTO cus)
        {
            Connection connecttion = new Connection();
            MySqlConnection con = new MySqlConnection(connecttion.Connect);
            MySqlCommand cmd = new MySqlCommand("DeleteCustomerByID", con);
            cmd.CommandType = System.Data.CommandType.StoredProcedure;

            cmd.Parameters.Add(new MySqlParameter("@customerid", cus.CustomerID));

            con.Open();
            cmd.ExecuteNonQuery();
            con.Close();

            return true;
        }
        #endregion

    }
}
